package persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import persistence.DBSession;

/**
 * @author: Humberto Rocha Loureiro (humbertorocha@gmail.com)
 * @modify: 
 */

public class QuoteEngineDAO { 

	private Connection conn = null;
	private ResultSet rs = null;
	private Statement stat = null;

	public List<QuoteVO> listQuote(String date) {
		List<QuoteVO> result = new ArrayList<QuoteVO>();
		conn = DBSession.getIstance().getConnection();
		PreparedStatement pstat = null;
		
		try {
			String sql = " select t1.stock, t2.name, t1.high, t1.low, t1.open, t1.close, t1.volume, t2.star " +
						 " from quote t1, stocks t2 " +
						 " where " +
						 "	 t1.date = ? " +
						 "  and t1.stock = t2.stock";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, date);
		
			rs = pstat.executeQuery();
			while(rs.next()) {
				QuoteVO quote = new QuoteVO();
				quote.setStock(rs.getString("stock"));
				quote.setName(rs.getString("name"));
				quote.setHigh(rs.getString("high"));
				quote.setLow(rs.getString("low"));
				quote.setOpen(rs.getString("open"));
				quote.setClose(rs.getString("close"));
				quote.setVolume(rs.getString("volume"));
				quote.setStar(rs.getInt("star"));
				
				result.add(quote);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				rs.close();
				pstat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public QuoteVO getQuote(String date, String stock) {
		conn = DBSession.getIstance().getConnection();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		QuoteVO quote = new QuoteVO();
		
		try {
			String sql = " select t1.stock, t2.name, t1.high, t1.low, t1.open, t1.close, t1.volume, t1.date, t2.star " +
						 " from quote t1, stocks t2 " +
						 " where " +
						 "	t1.date = ? " +
						 "	and t1.stock = ? " +
						 "  and t1.stock = t2.stock ";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, date);
			pstat.setString(2, stock);
		
			rs = pstat.executeQuery();
			if(rs.next()) {
				quote.setStock(rs.getString("stock"));
				quote.setName(rs.getString("name"));
				quote.setHigh(rs.getString("high"));
				quote.setLow(rs.getString("low"));
				quote.setOpen(rs.getString("open"));
				quote.setClose(rs.getString("close"));
				quote.setVolume(rs.getString("volume"));
				quote.setDate(rs.getString("date"));
				quote.setStar(rs.getInt("star"));
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				rs.close();
				pstat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return quote;
	}

	public String getLastDate(String stock) {
		String date = "";
		conn = DBSession.getIstance().getConnection();
		PreparedStatement pstat = null;
		try {
			String sql = " select max(date) " +
						 " from quote " +
						 " where " +
						 "	  stock = ? ";

			pstat = conn.prepareStatement(sql);
			pstat.setString(1, stock);
		
			rs = pstat.executeQuery();
			if(rs.next()) {
				date = rs.getString("date");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				rs.close();
				pstat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return date;
	}
	
	public void update(QuoteVO vo){
		try {
			conn = DBSession.getIstance().getConnection();
			PreparedStatement pstat = null;
			String sql = " update stocks set " +
						 "	 star = ? 		" +
						 " where stock = ? ";

			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, vo.getStar());
			pstat.setString(2, vo.getStock());
			
			pstat.execute();
			pstat.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}
